package com.hyacinth.utils.file;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {
	private static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
	/**
	 * 将集合数据处理成适应导出的数据格式
	 * 
	 * @param dataList
	 * @return
	 */
	private List<Object[]> dealWithObject(List<?> dataList) {
		List<Object[]> list = new ArrayList<Object[]>();
		for (Object o : dataList) {
			if(o instanceof Object[]){
				return (List<Object[]>) dataList;
			}
			list.add(getFiledValues(o));
		}
		return list;
	}

	/**
	 * 根据属性名获取属性值
	 * */
	private Object getFieldValueByName(String fieldName, Object o) {
		try {
			String firstLetter = fieldName.substring(0, 1).toUpperCase();
			String getter = "get" + firstLetter + fieldName.substring(1);
			Method method = o.getClass().getMethod(getter, new Class[]{});
			Object value = method.invoke(o, new Object[]{});
			return value;
		} catch (Exception e) {
			// log.error(e.getMessage(),e);
			log.error("根据属性名获取属性值异常。。。");
			return null;
		}
	}

	/**
	 * 获取属性名数组
	 * */
	private String[] getFiledName(Object o) {
		Field[] fields = o.getClass().getDeclaredFields();
		String[] fieldNames = new String[fields.length];
		for (int i = 0; i < fields.length; i++) {
			System.out.println(fields[i].getType());
			fieldNames[i] = fields[i].getName();
		}
		return fieldNames;
	}

	/**
	 * 获取对象的所有属性值，返回一个对象数组
	 * */
	private Object[] getFiledValues(Object o) {
		String[] fieldNames = this.getFiledName(o);
		Object[] value = new Object[fieldNames.length];
		for (int i = 0; i < fieldNames.length; i++) {
			value[i] = this.getFieldValueByName(fieldNames[i], o);
		}
		return value;
	}

	/**
	 * 根据流导出excel
	 * 
	 * @param out
	 * @param rowName
	 * @param dataList
	 */
	public static void exportFileByOutputStream(OutputStream out,
			List<String> rowName, List<?> dataList) {
		String[] rowNameStr = rowName.toArray(new String[0]);
		HSSFWorkbook workbook =new HSSFWorkbook();
		workbook = export(rowNameStr, dataList,workbook,null);
		try {
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 根据文件目标文件路径导出文件
	 * 
	 * @param fileFath
	 * @param rowName
	 * @param dataList
	 */
	public static void exportFileByFileFath(String fileFath, String[] rowName,
			List<?> dataList) {
		HSSFWorkbook workbook=new HSSFWorkbook();
		workbook = export(rowName, dataList,workbook,null);
		OutputStream out = null;
		try {
			out = new FileOutputStream(fileFath);
			workbook.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				out.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 
	 * @param rowName
	 *            xls表头名称
	 * @param dataList
	 *            存贮导出对象内容的集合
	 * @return HSSFWorkbook 返回一个xls工作簿对象
	 */
	public static HSSFWorkbook export(String[] rowName, List<?> dataList,HSSFWorkbook workbook,String sheetName) {
		if(dataList == null || dataList.size() <1){
			return workbook;
		}
		int count = dataList.size();
		int num1;
		if (count % 50000 == 0) {
			num1 = count / 50000;
		} else {
			num1 = count / 50000 + 1;
		}
		for(int k = 1; k <= num1; k++){
			// 创建初始工作表
			HSSFSheet sheet = workbook.createSheet("sheet" + k);
			if(sheetName != null){
				sheet = workbook.createSheet(sheetName + k);
			}
			HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
			HSSFCellStyle style = getStyle(workbook); // 单元格样式对象
			// 定义所需列数
			int columnNum = rowName.length;
			HSSFRow rowRowName = sheet.createRow(0); // 在索引2的位置创建行(最顶端的行开始的第二行)
			// 将列头设置到sheet的单元格中
			for (int n = 0; n < columnNum; n++) {
				HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
				cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
				HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
				cellRowName.setCellValue(text); // 设置列头单元格的值
				cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
			}
			ExcelUtils export = new ExcelUtils();
			List<Object[]> dataLists = export.dealWithObject(dataList);
			// 将查询出的数据设置到sheet对应的单元格中
			for (int i = 50000*k-50000; i < 50000*k && i<count; i++) {
				Object[] obj = dataLists.get(i);// 遍历每个对象
				HSSFRow row = sheet.createRow(i-50000*k+50001);// 创建所需的行数（从第二行开始写数据）
				for (int j = 0; j < obj.length; j++) {
					HSSFCell cell =  row.createCell(j, HSSFCell.CELL_TYPE_STRING);
					if (obj[j] != null) {
						cell.setCellValue(obj[j].toString()); // 设置单元格的值
					}
					cell.setCellStyle(style); // 设置单元格样式
				} 
			}
			// 让列宽随着导出的列长自动适应
			for (int colNum = 0; colNum < columnNum; colNum++) {
				int columnWidth = sheet.getColumnWidth(colNum) / 256;
				for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
					HSSFRow currentRow;
					// 当前行未被使用过
					if (sheet.getRow(rowNum) == null) {
						currentRow = sheet.createRow(rowNum);
					} else {
						currentRow = sheet.getRow(rowNum);
					}

					if (currentRow.getCell(colNum) != null) {
						HSSFCell currentCell = currentRow.getCell(colNum);
						if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
							int length = 0;
							try {
								length = currentCell.getStringCellValue()
										.getBytes().length;
							} catch (Exception e) {
								//e.printStackTrace();
							}
							if (columnWidth < length) {
								columnWidth = length;
							}
						}
					}
				}
				if (colNum == 0) {
					sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
				} else {
					sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
				}
			}
		}
		return workbook;
	}

	/*
	 * 列头单元格样式
	 */
	private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

		// 设置字体
		HSSFFont font = workbook.createFont();
		// 设置字体大小
		font.setFontHeightInPoints((short) 11);
		// 字体加粗
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		// 设置右边框;
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColor.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColor.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		return style;

	}

	/*
	 * 列数据信息单元格样式
	 */
	private static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
		// 设置字体
		HSSFFont font = workbook.createFont();
		// 设置字体大小
		// font.setFontHeightInPoints((short)10);
		// 字体加粗
		// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 设置字体名字
		font.setFontName("Courier New");
		// 设置样式;
		HSSFCellStyle style = workbook.createCellStyle();
		// 设置底边框;
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		// 设置底边框颜色;
		style.setBottomBorderColor(HSSFColor.BLACK.index);
		// 设置左边框;
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		// 设置左边框颜色;
		style.setLeftBorderColor(HSSFColor.BLACK.index);
		// 设置右边框;
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		// 设置右边框颜色;
		style.setRightBorderColor(HSSFColor.BLACK.index);
		// 设置顶边框;
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		// 设置顶边框颜色;
		style.setTopBorderColor(HSSFColor.BLACK.index);
		// 在样式用应用设置的字体;
		style.setFont(font);
		// 设置自动换行;
		style.setWrapText(false);
		// 设置水平对齐的样式为居中对齐;
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 设置垂直对齐的样式为居中对齐;
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

		return style;

	}

	/**
	 * 导入只有一个sheet页的excel文件，
	 * 
	 * @param filepath
	 * @return
	 */
	public static List<String[]> importOneSheet(String filepath) {
		try {
			return getSingleSheet(filepath, 0);
		} catch (FileNotFoundException e) {
			log.error("文件不存在......");
		} catch (IOException e) {
			log.error("excel文件解析异常......");
		}
		return null;
	}

	/**
	 * 导入含有多个sheet页的excel文件，
	 * 
	 * @param filepath
	 * @return
	 */
	public static Map<Integer, List<String[]>> importMultiSheet(String filepath) {

		File file = new File(filepath);
		Workbook workbook = null;
		Map<Integer, List<String[]>> returnList = new LinkedHashMap<Integer, List<String[]>>(); // <sheet页，每行对应
																								// 集合>
		try {
			workbook = new HSSFWorkbook(new FileInputStream(file));
			int sheetNo = workbook.getNumberOfSheets(); // 所有的sheet页
			for (int i = 0; i < sheetNo; i++) {
				returnList.put(i, getSingleSheet(filepath, i));
			}
			return returnList;
		} catch (FileNotFoundException e) {
			log.error("文件不存在......");
		} catch (IOException e) {
			log.error("excel文件解析异常......");
		}
		return null;
	}

	/**
	 * 
	 * @param file
	 * @param sheetIndex
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static List<String[]> getSingleSheet(File file, int sheetIndex) throws FileNotFoundException, IOException{
	    Workbook workbook = null;
        List<String[]> returnList = new ArrayList<String[]>(); // <表头，表头对应的列集合>
        workbook = new HSSFWorkbook(new FileInputStream(file));// 获得一个excel工作簿
        Sheet sheet = workbook.getSheetAt(sheetIndex); // 默认获取第0个sheet页
        int minRowIx = sheet.getFirstRowNum();
        int maxRowIx = sheet.getLastRowNum();
        for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
            Row row = sheet.getRow(rowIx);
            if (row == null) {
                continue;
            }
            short minColIx = row.getFirstCellNum();
            short maxColIx = row.getLastCellNum();
            String[] rowContext = new String[maxColIx - minColIx]; // 封装一行的数组
            for (short colIx = minColIx; colIx < maxColIx; colIx++) {
                Cell cell = row.getCell(new Integer(colIx));
                if (null == cell) {
                    cell = row.createCell(new Integer(colIx));
                }
                cell.setCellType(Cell.CELL_TYPE_STRING);
                String value = null == cell.getStringCellValue() ? "" : cell
                        .getStringCellValue();// 单元格的String的值
                rowContext[colIx] = value;
            }
            returnList.add(rowContext);
        }
        return returnList;
	}
	
	/**
	 * 
	 * @param filepath
	 *            文件路径，
	 * @param sheetIndex
	 *            sheet标签也下标
	 * @return
	 * @throws IOException
	 *             解析异常
	 * @throws FileNotFoundException
	 *             文件路径不存在
	 */
	public static List<String[]> getSingleSheet(String filepath, int sheetIndex)
			throws FileNotFoundException, IOException {
		File file = new File(filepath);
		Workbook workbook = null;
		List<String[]> returnList = new ArrayList<String[]>(); // <表头，表头对应的列集合>
		workbook = new HSSFWorkbook(new FileInputStream(file));// 获得一个excel工作簿
		Sheet sheet = workbook.getSheetAt(sheetIndex); // 默认获取第0个sheet页
		int minRowIx = sheet.getFirstRowNum();
		int maxRowIx = sheet.getLastRowNum();
		for (int rowIx = minRowIx; rowIx <= maxRowIx; rowIx++) {
			Row row = sheet.getRow(rowIx);
			if (row == null) {
				continue;
			}
			short minColIx = row.getFirstCellNum();
			short maxColIx = row.getLastCellNum();
			String[] rowContext = new String[maxColIx - minColIx]; // 封装一行的数组
			for (short colIx = minColIx; colIx < maxColIx; colIx++) {
				Cell cell = row.getCell(new Integer(colIx));
				if (null == cell) {
					cell = row.createCell(new Integer(colIx));
				}
				cell.setCellType(Cell.CELL_TYPE_STRING);
				String value = null == cell.getStringCellValue() ? "" : cell
						.getStringCellValue();// 单元格的String的值
				rowContext[colIx] = value;
			}
			returnList.add(rowContext);
		}
		return returnList;
	}

	/**
	 * Excel的导出
	 * 
	 * @param fileDir
	 *            在服务端生成文件的目录
	 * @param rowName
	 *            Excel文件的列标题
	 * @param objectList
	 *            存放的数据集合
	 * @return 文件绝对路径
	 */
	public static String export(String fileDir, String[] rowName,
			List<?> objectList) {
		HSSFWorkbook workbook =new HSSFWorkbook();
		workbook = export(rowName, objectList,workbook,null);
		//HSSFWorkbook workbook = export(rowName, objectList); // 生成excel对象
		String fileName =fileDir+File.separator+System.currentTimeMillis() + ".xls";
		File fileDirs = new File(fileDir);
		if(!fileDirs.exists()){
			fileDirs.mkdir();
		}
		File file = new File(fileName);
		if (!file.exists()) {

			try {
				file.createNewFile();
			} catch (IOException e) {
				log.error("文件创建异常。。。");
				e.printStackTrace();
				return null;
			}
		}
		try {
			OutputStream out = new FileOutputStream(file);
			workbook.write(out);
		} catch (Exception e) {
			log.error("文件不存在......");
			return null;
		}
		return fileName;
	}
}
